﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using CommonLibrary.DB;
using CommonLibrary.DataModel.Car;
using System.Data;

namespace ServiceApplication.Dao.Car
{
    public class ApplyDao
    {
        public int AddEntity(ApplyEntity applyEntity)
        {
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@apply_id", applyEntity.apply_id));
            parameterList.Add(new SqlParameter("@apply_stime", applyEntity.apply_stime));
            parameterList.Add(new SqlParameter("@apply_etime", applyEntity.apply_etime));
            parameterList.Add(new SqlParameter("@apply_time", applyEntity.apply_time));
            parameterList.Add(new SqlParameter("@apply_person", applyEntity.apply_person));
            parameterList.Add(new SqlParameter("@car_id", applyEntity.car_id));
            parameterList.Add(new SqlParameter("@sqr_id", applyEntity.sqr_id));
            parameterList.Add(new SqlParameter("@czr_id", applyEntity.czr_id));
            parameterList.Add(new SqlParameter("@apply_result", applyEntity.apply_result));
            parameterList.Add(new SqlParameter("@apply_reply", applyEntity.apply_reply));
            parameterList.Add(new SqlParameter("@apply_cztime", applyEntity.apply_cztime));


            string sql = "insert into car_apply (apply_stime,apply_etime,apply_time,apply_person,car_id,sqr_id,czr_id, apply_result,apply_reply,apply_cztime) values " +
                " (@apply_stime,@apply_etime,@apply_time,@apply_person,@car_id,@sqr_id,@czr_id,@apply_result,@apply_reply,@apply_cztime); " +
                " select @@identity";
            string dept_id = DBExecuteUtil.getUniqueResult(sql, parameterList.ToArray());
            return int.Parse(dept_id);
        }

        public void DeleteEntity(int apply_id)
        {
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@apply_id", apply_id));
            string sql = "delete from car_apply where apply_id=@apply_id ";
            DBExecuteUtil.executeSqlWithPar(sql, parameterList.ToArray());
        }

        public void UpdateEntity(ApplyEntity applyEntity)
        {
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@apply_id", applyEntity.apply_id));
            parameterList.Add(new SqlParameter("@apply_stime", applyEntity.apply_stime));
            parameterList.Add(new SqlParameter("@apply_etime", applyEntity.apply_etime));
            parameterList.Add(new SqlParameter("@apply_time", applyEntity.apply_time));
            parameterList.Add(new SqlParameter("@apply_person", applyEntity.apply_person));
            parameterList.Add(new SqlParameter("@car_id", applyEntity.car_id));
            parameterList.Add(new SqlParameter("@sqr_id", applyEntity.sqr_id));
            parameterList.Add(new SqlParameter("@czr_id", applyEntity.czr_id));
            parameterList.Add(new SqlParameter("@apply_result", applyEntity.apply_result));
            parameterList.Add(new SqlParameter("@apply_reply", applyEntity.apply_reply));
            parameterList.Add(new SqlParameter("@apply_cztime", applyEntity.apply_cztime));

            string sql = "update car_apply set apply_stime=@apply_stime,apply_etime=@apply_etime,apply_time=@apply_time,apply_person=@apply_person," +
                " car_id=@car_id,sqr_id=@sqr_id,czr_id=@czr_id, apply_result=@apply_result,apply_reply=@apply_reply,apply_cztime=@apply_cztime where apply_id=@apply_id";

            DBExecuteUtil.executeSqlWithPar(sql, parameterList.ToArray());
        }

        public ApplyEntity QueryEntity(int apply_id)
        {
            ApplyEntity applyEntity = null;
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@apply_id", apply_id));
            string sql = "select apply_id,apply_stime,apply_etime,apply_time,apply_person,car_id,sqr_id,czr_id, apply_result,apply_reply,apply_cztime " +
                " from car_apply where apply_id=@apply_id";

            var result = DBExecuteUtil.querySqlArray(sql, parameterList.ToArray());
            if (result == null) return null;

            applyEntity = new ApplyEntity();
            applyEntity.apply_id = int.Parse(result[0][0]);
            applyEntity.apply_stime = result[0][1];
            applyEntity.apply_etime = result[0][2];
            applyEntity.apply_time = result[0][3];
            applyEntity.apply_person = result[0][4];
            applyEntity.car_id = int.Parse(result[0][5]);
            applyEntity.sqr_id = int.Parse(result[0][6]);
            applyEntity.czr_id = int.Parse(result[0][7]);
            applyEntity.apply_result = int.Parse(result[0][8]);
            applyEntity.apply_reply = result[0][9];
            applyEntity.apply_cztime = result[0][10];
            return applyEntity;
        }

        public DataTable Query(ApplyEntity applyEntity)
        {
            List<SqlParameter> parameterList = new List<SqlParameter>();

            string sql = " select a.apply_id 申请编码,apply_stime 开始时间,apply_etime 结束时间,apply_time 申请时间,apply_person 申请事由,  " +
                             " a.car_id 车辆编码,c.car_name 车辆名称,sqr_id 申请人编码,u1.user_name 申请人,czr_id 操作人编码,u2.user_name 操作人," +
                             " apply_result 申请结果,apply_reply 申请回复,apply_cztime 操作时间 " +
                             " from jc_user u1 ,car_apply a left join car_car c on a.car_id=c.car_id   " +
                             "                               left join jc_user u2 on a.czr_id=u2.user_id " +
                             "  where a.sqr_id=u1.user_id and a.car_id=a.car_id   ";
            if (!string.IsNullOrEmpty(applyEntity.apply_stime))
            {
                parameterList.Add(new SqlParameter("@apply_stime", applyEntity.apply_stime));
                sql += " and apply_stime>@apply_stime ";
            }
            if (!string.IsNullOrEmpty(applyEntity.apply_etime))
            {
                parameterList.Add(new SqlParameter("@apply_etime", applyEntity.apply_etime));
                sql += " and apply_etime<@apply_etime ";
            }
            if (applyEntity.car_id > 0)
            {
                parameterList.Add(new SqlParameter("@car_id", applyEntity.car_id));
                sql += " and a.car_id=@car_id ";
            }
            if (applyEntity.sqr_id > 0)
            {
                parameterList.Add(new SqlParameter("@sqr_id", applyEntity.sqr_id));
                sql += " and a.sqr_id=@sqr_id ";
            }
            sql += " order by apply_result,apply_time desc";
            var dt = DBExecuteUtil.querySqlTable(sql, parameterList.ToArray());
            return dt;
        }


        public DataTable QueryAll()
        {
            string sql = "select a.apply_id 申请编码,apply_stime 开始时间,apply_etime 结束时间,apply_time 申请时间,apply_person 申请事由, " +
                             " a.car_id 车辆编码,c.car_name 车辆名称,sqr_id 申请人,czr_id 操作人, apply_result 申请结果,apply_reply 申请回复,apply_cztime 操作时间 " +
                             " from car_apply a,car_car c where a.car_id=c.car_id ";

            return DBExecuteUtil.querySqlTable(sql);
        }
        public DataTable QueryByCondition(string sqr_id, string czr_id, string apply_result, string timetype, string stime, string etime)
        {
            List<SqlParameter> parameterList = new List<SqlParameter>();

            string sql = " select a.apply_id 申请编码,apply_stime 开始时间,apply_etime 结束时间,apply_time 申请时间,apply_person 申请事由,  " +
                             " a.car_id 车辆编码,c.car_name 车辆名称,sqr_id 申请人编码,u1.user_name 申请人,czr_id 操作人编码,u2.user_name 操作人," +
                             " apply_result 申请结果,apply_reply 申请回复,apply_cztime 操作时间 " +
                             " from jc_user u1 ,car_apply a left join car_car c on a.car_id=c.car_id   " +
                             "                               left join jc_user u2 on a.czr_id=u2.user_id " +
                             "  where a.sqr_id=u1.user_id and a.car_id=a.car_id   ";
            if (!string.IsNullOrEmpty(sqr_id))
            {
                parameterList.Add(new SqlParameter("@sqr_id", sqr_id));
                sql += " and sqr_id=@sqr_id ";
            }
            if (!string.IsNullOrEmpty(czr_id))
            {
                parameterList.Add(new SqlParameter("@czr_id", czr_id));
                sql += " and czr_id=@czr_id ";
            }
            if (!string.IsNullOrEmpty(apply_result))
            {
                parameterList.Add(new SqlParameter("@apply_result", apply_result));
                sql += " and apply_result=@apply_result ";
            }

            if (!string.IsNullOrEmpty(stime))
            {
                if (timetype == "time")
                {
                    parameterList.Add(new SqlParameter("@stime", stime));
                    sql += " and apply_time>=@stime ";
                }
                else if (timetype == "stime")
                {
                    parameterList.Add(new SqlParameter("@stime", stime));
                    sql += " and apply_stime>=@stime ";
                }
                else if (timetype == "etime")
                {
                    parameterList.Add(new SqlParameter("@stime", stime));
                    sql += " and apply_etime>=@stime ";
                }
                else if (timetype == "cztime")
                {
                    parameterList.Add(new SqlParameter("@stime", stime));
                    sql += " and apply_cztime>=@stime ";
                }
            }
            if (!string.IsNullOrEmpty(etime))
            {
                if (timetype == "time")
                {
                    parameterList.Add(new SqlParameter("@etime", etime));
                    sql += " and apply_time<=@etime ";
                }
                else if (timetype == "stime")
                {
                    parameterList.Add(new SqlParameter("@etime", etime));
                    sql += " and apply_stime<=@etime ";
                }
                else if (timetype == "etime")
                {
                    parameterList.Add(new SqlParameter("@etime", etime));
                    sql += " and apply_etime<=@etime ";
                }
                else if (timetype == "cztime")
                {
                    parameterList.Add(new SqlParameter("@etime", etime));
                    sql += " and apply_cztime<=@etime ";
                }
            }

            sql += " order by apply_result,apply_time desc";
            var dt = DBExecuteUtil.querySqlTable(sql, parameterList.ToArray());
            return dt;
        }

        public DataTable QueryForConflict(string stime, string etime, int car_id)
        {
            List<SqlParameter> parameterList = new List<SqlParameter>();

            string sql = " select a.apply_id 申请编码,apply_stime 开始时间,apply_etime 结束时间,apply_time 申请时间,apply_person 申请事由,  " +
                             " a.car_id 车辆编码,c.car_name 车辆名称,sqr_id 申请人编码,u1.user_name 申请人,czr_id 操作人编码,u2.user_name 操作人," +
                             " apply_result 申请结果,apply_reply 申请回复,apply_cztime 操作时间 " +
                             " from jc_user u1 ,car_apply a left join car_car c on a.car_id=c.car_id   " +
                             "                               left join jc_user u2 on a.czr_id=u2.user_id " +
                             "  where a.sqr_id=u1.user_id and a.car_id=a.car_id  and a.apply_result=1  ";

            parameterList.Add(new SqlParameter("@stime", stime));
            parameterList.Add(new SqlParameter("@etime", etime));
            parameterList.Add(new SqlParameter("@car_id", car_id));
            sql += " and (( @stime>=apply_stime and @stime<=apply_etime) or (@etime>=apply_stime and @etime<=apply_etime) or (@stime<=apply_stime and @etime>=apply_etime) or (@stime=apply_stime and @etime=apply_etime)) ";
            sql += " and a.car_id=@car_id ";
            sql += " order by apply_result,apply_time desc";
            var dt = DBExecuteUtil.querySqlTable(sql, parameterList.ToArray());
            return dt;
        }

        //public DataTable QueryForCarState(string car_id, string stime,string etime)
        //{
        //    List<SqlParameter> parameterList = new List<SqlParameter>();

        //    string sql = " select a.apply_id 申请编码,apply_stime 开始时间,apply_etime 结束时间,apply_time 申请时间,apply_person 申请事由,  " +
        //                     " a.car_id 车辆编码,c.car_name 车辆名称,sqr_id 申请人编码,u1.user_name 申请人,czr_id 操作人编码,u2.user_name 操作人," +
        //                     " apply_result 申请结果,apply_reply 申请回复,apply_cztime 操作时间 " +
        //                     " from jc_user u1 ,car_apply a left join car_car c on a.car_id=c.car_id   " +
        //                     "                               left join jc_user u2 on a.czr_id=u2.user_id " +
        //                     "  where a.sqr_id=u1.user_id and a.car_id=a.car_id  and a.apply_result=1  ";

        //    parameterList.Add(new SqlParameter("@car_id", car_id));
        //    parameterList.Add(new SqlParameter("@stime", stime));
        //    parameterList.Add(new SqlParameter("@etime", etime));
        //    sql += " and (( @stime>apply_stime and @stime<apply_etime) or (@etime>apply_stime and @etime<apply_etime) or (@stime<apply_stime and @etime>apply_etime) or (@stime=apply_stime and @etime=apply_etime)) ";
        //    sql += " and a.car_id=@car_id ";
        //    sql += " order by apply_result,apply_time desc";
        //    var dt = DBExecuteUtil.querySqlTable(sql, parameterList.ToArray());
        //    return dt;
        //}

    }
}
